
/*********************
 **  CreateTime: 2006-7-26 
 **********************/

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;

using EntLibEC.Cmn;
using EntLibEC.Info.PIM;


namespace EntLibEC.Dac.PIM
{
    /// <summary>
    /// Contains the the data access methods for CategoryTemplateDA.
    /// </summary>
    public abstract class CategoryTemplateDA
    {
        #region Sql Script

        private const string SQL_GET_ALL_CATEGORYTEMPLATE
            = @"SELECT a.SysNo,a.CategorySysNo,a.LastEditUserSysNo,a.LastEditTime,b.C3Name AS CategoryName,c.UserName AS LastEditUsername
				FROM PIM_CategoryTemplate a (NOLOCK) 
                LEFT JOIN Ent_Category3 b (NOLOCK) ON a.CategorySysNo = b.SysNo
                LEFT JOIN Ent_Sys_User c (NOLOCK) ON a.LastEditUserSysNo = c.SysNo";

        private const string SQL_GET_CATEGORYTEMPLATE
            = @"SELECT a.SysNo,a.CategorySysNo,a.LastEditUserSysNo,a.LastEditTime,b.C3Name AS CategoryName,c.UserName AS LastEditUsername
				FROM PIM_CategoryTemplate a (NOLOCK)
                LEFT JOIN Ent_Category3 b (NOLOCK) ON a.CategorySysNo = b.SysNo
                LEFT JOIN Ent_Sys_User c (NOLOCK) ON a.LastEditUserSysNo = c.SysNo
				WHERE a.CategorySysNo = @CategorySysNo";

        private const string SQL_INSERT_CATEGORYTEMPLATE = "INSERT INTO PIM_CategoryTemplate(CategorySysNo,LastEditUserSysNo,LastEditTime) VALUES (@CategorySysNo,@LastEditUserSysNo,getdate()) SELECT @@IDENTITY";
        private const string SQL_UPDATE_CATEGORYTEMPLATE = "UPDATE PIM_CategoryTemplate SET LastEditUserSysNo=@LastEditUserSysNo,LastEditTime=getdate() WHERE SysNo=@SysNo";
        #endregion

        protected static CategoryTemplateInfo Map(DataRow row)
        {
            CategoryTemplateInfo info = new CategoryTemplateInfo();

            info.SysNo = Convert.ToInt32(row["SysNo"]);
            info.CategorySysNo = Convert.ToInt32(row["CategorySysNo"]);
            info.LastEditUserSysNo = Convert.ToInt32(row["LastEditUserSysNo"]);
            info.LastEditTime = Convert.ToDateTime(row["LastEditTime"]);
            
            info.CategoryName = row["CategoryName"].ToString();
            info.LastEditUsername = row["LastEditUsername"].ToString();

            return info;
        }

        public static CategoryTemplateInfo GetCategoryTemplateByCategorySysNo(int SysNo)
        {
            SqlParameter[] parms = new SqlParameter[]{
                new SqlParameter("@CategorySysNo", SqlDbType.Int)
            };
            parms[0].Value = SysNo;

            DataTable dt = DataAccessFacade.GetDataTable(SQL_GET_CATEGORYTEMPLATE, parms);
            if (dt == null || dt.Rows.Count != 1)
                return null;

            return Map(dt.Rows[0]);
        }

        public static int InsertCategoryTemplate(CategoryTemplateInfo info)
        {
            SqlParameter[] parms = new SqlParameter[]{
                new SqlParameter("@CategorySysNo", SqlDbType.Int),
                new SqlParameter("@LastEditUserSysNo", SqlDbType.Int)
            };

            parms[0].Value = info.CategorySysNo;
            parms[1].Value = info.LastEditUserSysNo;

            object obj = DataAccessFacade.ExecScalarSqlCommand(SQL_INSERT_CATEGORYTEMPLATE, parms);
            return Convert.ToInt32(obj);
        }

        public static void UpdateCategoryTemplate(CategoryTemplateInfo info)
        {
            SqlParameter[] parms = new SqlParameter[]{
                new SqlParameter("@SysNo", SqlDbType.Int),
                new SqlParameter("@LastEditUserSysNo", SqlDbType.Int)
            };

            parms[0].Value = info.SysNo;
            parms[1].Value = info.LastEditUserSysNo;

            DataAccessFacade.ExecSqlCommand(SQL_UPDATE_CATEGORYTEMPLATE, parms);
        }
    }
}
